<?php
namespace app\common\controller;
use think\Controller;
use think\Exception;

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/8/29
 * Time: 14:08
 */
class Excel extends Controller
{
    private $filePath;// 要读取的文件的路径

    /**
     *识别文件是否合法并返回合适的reader对象
     */
    public function index() {
        try{
            $ent_arr = explode(".",$this->filePath);
            $file_ext = end($ent_arr);  //获取文件扩展名
            if ($file_ext === 'xlsx') {
                $readerObj = \PHPExcel_IOFactory::createReader('Excel2007');
            }else{
                //识别旧版excel
                $readerObj = \PHPExcel_IOFactory::createReader('Excel5');
            }
            return $readerObj;
        }catch (\Exception $e) {
            return $this->error($e->getMessage(),"admin/Index/welcome",'',1000);
            //
        }
    }

    /**
     *读取excel表内容
     */
    public function getSheetsContent() {
        $readerObj = $this->index();
        $excelObj = $readerObj->load($this->filePath);
        //获取工作表个数
        $sheetCount = $excelObj->getSheetCount();
        $datas = [];
        for ($i=0;$i<$sheetCount;$i++) {
            $data_arr = [];
            //当前sheet
            $sheet = $excelObj->getSheet($i);
//            $data_arr[] = $sheet->toArray();
            //获取行和列总数
//            $highestRow = $sheet->getHighestRow();
//            $highestColumn = $sheet->getHighestColumn();
            foreach ($sheet->getRowIterator() as $key => $row) {
                $cellIterator = $row->getCellIterator();
//                $cellIterator->setIterateOnlyExistingCells(false);
                foreach ($cellIterator as $cell) {
                    $columnValue = (string)$cell->getValue();//获取数据
                    if (!empty($columnValue)) {
                        $data_arr[$key-1][] = $columnValue;
                    }
                }
            }
            $datas[$i] = $data_arr;
        }
        //整合所有工作表数据
        $mergeDatas = $datas[0];
        foreach ($datas as $k => $data) {
            if ($k<count($datas)-1) {
                $mergeDatas = array_merge($mergeDatas,$datas[$k+1]);
            }
        }
        return $mergeDatas;   //目前只支持单表
    }

    /**
     *获取Excel表字段信息
     */
    public function getFields() {
        $datas = $this->getSheetsContent();
        $fields = null;
        foreach ($datas as $k => $row) {
            if ($k<count($datas)-1) {
                if (count($row)==count($datas[$k+1])) {
                    $fields = $row;
                    break;
                }
            }
        }
        return $fields;
    }

    /**
     * 整理excel记录订单数据，把相同订单中相同的信息和不同的信息区分开来
     * @param $data_arr 订单数据集
     * @param $order_index 订单所在下标
     * @param $mode 为假时返回按订单相同来分组的数据集，为真则返回详细区分后的结果
     */
    public function getSameOrder($data_arr, $order_index,$mode=true) {
        foreach ($data_arr as $k => $row) {
            //判断该行记录是否是订单
            if (strlen($row[$order_index])>18) {
                $orderID = $row[$order_index];
                //获取相同订单的下标
                $data_arr_rev[$orderID][] = $k;
            }
        }

        //分别组装相同订单记录到新的数组
        $i= 0;
        foreach ($data_arr_rev as $record) {
            foreach ($record as $index) {
                $data_arr_new[$i][] = $data_arr[$index];
            }
            $i++;
        }
        if (!$mode) {
            return $data_arr_new;
        }

        //提取相同订单中相同的信息
        $datas_new = [];
        foreach ($data_arr_new as $x => $records) {
            $arr_same = array();
            foreach ($records as $k => $row) {
                if ($k<count($records)-1){
                    for ($i=$k+1;$i<count($records);$i++) {
                        //获取相同订单的交集
                        $arr_same[] = array_intersect($records[$k],$records[$i]);
                        if (count($arr_same)>1) {
                            //只取交集最小的项，保证订单信息的准确性
                            count($arr_same[0])<count($arr_same[1]) ? array_pop($arr_same) : array_shift($arr_same);
                        }
                    }
                }
            }
            if (!empty($arr_same)) {
                $datas_new[$x]['same']  = $arr_same[0];
                //获取相同订单相同信息列项的坐标
                $arr_same_key = null;
                foreach ($arr_same[0] as $k => $val) {
                    $arr_same_key[] = $k;
                }
                //提取相同订单中不同的信息
                $arr_diff = null;
                foreach ($records as $row) {
                    $arr_diff[$order_index] = $arr_same[0][$order_index];
                    foreach ($row as $j => $value) {
                        if (!in_array($j,$arr_same_key)) {
                            $arr_diff[$j] = $value;
                        }
                    }
                    //组装新的订单信息
                    $datas_new[$x]['diff'] []= $arr_diff;
                }
            }else{
                //单条订单信息原封不动放入新数组
                $datas_new[$x] = $records[0];
            }
        }
        return $datas_new;
    }

    /**
     * 导出数据库内容到excel表
     * @param $datas 数据库内容，类型为数组
     */
    public function exportExcel($datas,$excelType=false) {
        $excelObj = new \PHPExcel();
        /*以下是一些设置，作者  标题之类*/
        $excelObj
            ->getProperties()->setCreator("Admin")
            ->setLastModifiedBy("Admin")
//            ->setTitle(iconv("utf-8","gb2312","订单信息EXCEL导出"))
//            ->setSubject("订单信息EXCEL导出")
//            ->setDescription("备份数据")
            ->setKeywords("order")
            ->setCategory("result file");

        //设置sheet标题
        $excelObj->getActiveSheet()->setTitle('Test');
        //创建一个新的工作空间(sheet)
//        $excelObj->createSheet();
        //设置当前活动的sheet
        $excelObj->setActiveSheetIndex(0);
        $execelSheetObj = $excelObj->getActiveSheet(0);
        //单元格样式数组
        $all_style = array(
            'alignment' => array(
                'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER, //横向居中
                'vertical'   => \PHPExcel_Style_Alignment::VERTICAL_CENTER, //纵向居中
//                'wrap'       => true  //自动换行
            )
        );
        $title_style = [
            "font" => [
                "bold" => true
            ] ,
            "borders" => [
                "allborders" => [
                    "style" => \PHPExcel_Style_Border::BORDER_THIN
                ]
            ]
        ];
        $rowNums = count($datas)+2;
        $execelSheetObj->getStyle("A1:M".$rowNums)->applyFromArray($all_style);
        $execelSheetObj->getStyle("A1:M2")->applyFromArray($title_style);
        //合并单元格
        $execelSheetObj->mergeCells('A1:F1');
        $execelSheetObj->mergeCells('G1:M1');
        //设置标题值
        $execelSheetObj
            ->setCellValue('A1','订单头信息')
            ->setCellValue('G1','订单详细信息');
        $title = [
            '订单编号',
            '收货人名称',
            '收货人电话',
            '身份证',
            '收货详细地址',
            '备注',
            '商品名称',
            '商品编号',
            '商品批次',
            '数量',
            '售价',
            '币种',
            'CNY汇率'
        ];
//        $execelSheetObj->fromArray($title,null,"A2");
//      先在sheet生成要新增的数据行数
        $num_rows = count($datas);
        $last_row = $execelSheetObj->getHighestRow();
        $rows = $last_row + 1;
        $execelSheetObj->insertNewRowBefore($rows, $num_rows);
        //写入数据库内容
        //需要写入sheet的数据字段
        $datasKey = [
            'order_no',
            'consignee',
            'consignee_telephone',
            'buyer_id_number',
            'consignee_address',
            'note',
            'item_name',
            'item_no',
            'item_describe',
            'qty',
            'price',
            'currency',
            'listnote',
            ];
        //提取所需数据字段的数据
        $newDatas = null;
        foreach ($datas as $k => $val) {
            foreach ($datasKey as $key) {
                $newDatas[$k][] = $val[$key];
            }
        }
        //拼装标题
        array_unshift($newDatas,$title);
        //填入sheet单元格
        $execelSheetObj->fromArray($newDatas,null,"A2");


//      设置sheet的行高和列宽
        $spec_arr = ["1"=>"25",2=>"25"];
        $this->setRowsHeight($execelSheetObj,$rowNums,$spec_arr,"22.5");

        $spec_arr = ["B"=>20,"E"=>35,"G"=>45,"J"=>10,"K"=>10,"L"=>10,"M"=>10];
        $this->setColsWidth($execelSheetObj,13,$spec_arr);

//        保存文件在本地
        $outputFileName="output.xlsx";
        $objWriter = \PHPExcel_IOFactory::createWriter($excelObj, 'Excel2007');
        $objWriter->save($outputFileName);

        // 生成2003excel格式的xls文件或2007的xlsx文件
        $excelType == true ? $type = "Excel2007" : $type = "Excel5";
        $excelType == true ? $type_exts = "xlsx" : $type_exts = "xls";
//        header('Content-Type: application/vnd.ms-order');
//        header('Content-Disposition: attachment;filename="01simple.'.$type_exts.'"');
//        header('Cache-Control: max-age=0');
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition: attachment;filename="01simple.'.$type_exts.'"');
        header("Content-Transfer-Encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter = \PHPExcel_IOFactory::createWriter($excelObj, $type);
        $objWriter->save('php://output');
    }

    /**
     * 设置sheet的各列宽度
     * @param $execelSheetObj           当前sheet对象
     * @param $colNums                  总列数(目前只支持到A-ZZ列)
     * @param $spec_arr                 个别列宽设置，格式为["A"=>30,"B"=>30,...]
     * @param string $defaultWidth      设置默认宽度，默认为"true"，否则按传入的参数来设置宽度
     */
    public function setColsWidth($execelSheetObj, $colNums, $spec_arr, $defaultWidth="true") {
        //按十进制对应的ascii值进行列值循环
        $defaultWidth === "true" ? $set_way = "setAutoSize" : $set_way = "setWidth" ;
        $spec_keys_arr = array_keys($spec_arr);
        $k = 64;
        for ($i=65;$i<65+$colNums;$i++) {
            if ($i>90) {
                $i = 65;
                $colNums-=26;
                $k++;
                if ($k>90) {
                    return false;
                }
                $colKey = chr($k).chr($i);
            }elseif ($k>64) {
                $colKey = chr($k).chr($i);
            }else{
                $colKey = chr($i);
            }
            if(!in_array($colKey,$spec_keys_arr)){
                $execelSheetObj->getColumnDimension($colKey)->$set_way($defaultWidth);
            }
        }
        //重新定义个别列的列宽
        if (count($spec_arr)>1) {
            foreach ($spec_arr as $k => $val) {
                $execelSheetObj->getColumnDimension($k)->setWidth($val);
            }
        }
    }

    /**
     * 设置sheet的各行高度
     * @param $execelSheetObj           当前sheet对象
     * @param $rowNums                  总行数
     * @param $spec_arr                 个别行高设置，格式为["1"=>30,"5"=>30,...]
     * @param string $defaultHeight      设置默认高度，默认为"-1"，否则按传入的参数来设置高度
     **/
    public function setRowsHeight($execelSheetObj, $rowNums, $spec_arr, $defaultHeight="-1") {
        $spec_keys_arr = array_keys($spec_arr);
        for ($i=1;$i<=$rowNums;$i++) {
            if(!in_array($i,$spec_keys_arr)){
                $execelSheetObj->getRowDimension($i)->setRowHeight($defaultHeight);
            }
        }
        //重新定义个别行高
       if (count($spec_arr)>1) {
           foreach ($spec_arr as $k => $val) {
               $execelSheetObj->getRowDimension($k)->setRowHeight($val);
           }
       }
    }

    /**
     * @return mixed
     */
    public function getFilePath()
    {
        return $this->filePath;
    }

    /**
     * @param mixed $filePath
     */
    public function setFilePath($filePath)
    {
        $this->filePath = $filePath;
    }
}